﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using System.Collections;


namespace Pc_Vision.DAOFactorys
{
    public class DbConnection
    {
        public MySqlConnection conn;
        public DbConnection()
        {
            Connect("localhost", "pcVision", "root", "loltosalol");
        }

        public void Connect(string server, string db, string username, string pass)
        {
            string connString = "SERVER=" + server + ";" + "DATABASE=" + db + ";" + "UID=" + username + ";" + "PASSWORD=" + pass + ";";
            conn = new MySqlConnection(connString);
        }

        public void InsertDB(string query)
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = query;
            cmd.Connection = conn;
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public void UpdateDB(string sql)
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = sql;
            cmd.Connection = conn;
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public void DeleteDB(string sql)
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = sql;
            cmd.Connection = conn;
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public ArrayList GetAllCategories(string sql)
        {
            ArrayList arr = new ArrayList();

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            conn.Open();

            MySqlDataReader dataReader = cmd.ExecuteReader();

            while (dataReader.Read())
            {
                Model.Categorie c = new Model.Categorie();
                c.categorieName = dataReader["Categorienaam"].ToString();
                c.categorieDescription = dataReader["Omschrijving"].ToString();
                c.id = dataReader["Categorieid"].ToString();

                arr.Add(c);
            }
            dataReader.Close();
            conn.Close();
            return arr;
        }

        public ArrayList GetAllAdmins(string sql)
        {
            ArrayList arr = new ArrayList();

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            conn.Open();

            MySqlDataReader dataReader = cmd.ExecuteReader();

            while (dataReader.Read())
            {
                Model.Admin a = new Model.Admin();
                a.username = dataReader["Gebruikersnaam"].ToString();
                a.password = dataReader["Wachtwoord"].ToString();
                a.id = dataReader["Adminid"].ToString();
                arr.Add(a);
            }
            dataReader.Close();
            conn.Close();
            return arr;
        }
        public ArrayList GetAllProduct(string sql)
        {
            ArrayList arr = new ArrayList();

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            conn.Open();

            MySqlDataReader dataReader = cmd.ExecuteReader();

            while (dataReader.Read())
            {
                Model.Product p = new Model.Product();
                p.articleNumber = dataReader["Artikelnummer"].ToString();
                p.name = dataReader["Productnaam"].ToString();
                p.buyIn = dataReader["Inkoopprijs"].ToString();
                p.sellPrice = dataReader["Verkoopprijs"].ToString();
                p.inStock = dataReader["Opvoorraad"].ToString();
                p.productDescription = dataReader["Productomschrijving"].ToString();
                p.systemType = dataReader["Systeemtype"].ToString();
                p.processor = dataReader["Processor"].ToString();
                p.memory = dataReader["Geheugen"].ToString();
                p.storage = dataReader["Opslag"].ToString();
                p.opticalDisk = dataReader["Optischeschijf"].ToString();
                p.display = dataReader["Display"].ToString();
                p.videoCard = dataReader["Videokaart"].ToString();
                p.networkCard = dataReader["Netwerkkaart"].ToString();
                p.battery = dataReader["Batterij"].ToString();
                p.dimension = dataReader["Afmetingen"].ToString();
                p.weight = dataReader["Gewicht"].ToString();
                p.warranty = dataReader["Garantie"].ToString();
                p.actionPrice = dataReader["Actieprijs"].ToString();
                p.rating = dataReader["Rating"].ToString();

                arr.Add(p);
            }
            dataReader.Close();
            conn.Close();
            return arr;
        }
        public ArrayList GetAllActies(string sql)
        {
            ArrayList arr = new ArrayList();

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            conn.Open();

            MySqlDataReader dataReader = cmd.ExecuteReader();

            while (dataReader.Read())
            {
                Model.Actie a = new Model.Actie();
                a.actieNaam = dataReader["Actienaam"].ToString();
                a.startTijd = dataReader["Starttijd"].ToString();
                a.eindTijd = dataReader["Eindtijd"].ToString();
                a.actieId = dataReader["Actieid"].ToString();
                arr.Add(a);
            }
            dataReader.Close();
            conn.Close();
            return arr;
        }

    }
}